<?php
namespace App\Models\Msg;

use Illuminate\Database\Eloquent\Model;
use DB;

class msgM extends Model
{
    
    /**
     * 获得用户 未读消息 个数
     * @param unknown $userid
     */
    static  public function unreadMsgNum($userid){
        $msgFollowT=msgAddFollow::$STable;
        $msgIcnBelikedT=msgIcnBelikedM::$STable;
        
        $msgCmtT=msgCommentM::$STable;
        $msgBeStoredT=msgBestoredM::$STable;
        
        $msgCmtBelikedT=msgCmtBelikedM::$STable;
        $msgRp=msgRpM::$STable;

        
        $sql="select 
            (
            (select count(id) from $msgFollowT where userid =$userid and ispushed =false) +
            (select count(id) from $msgIcnBelikedT where belikeduserid =$userid and  ispushed =false) +
            
            (select count(id) from $msgCmtT where becmtuserid =$userid and ispushed =false) +  
            (select count(id) from $msgBeStoredT where bestoreduserid =$userid and ispushed =false)  +
            
            (select count(id) from $msgCmtBelikedT where belikeduserid=$userid and ispushed =false)  +
            (select count(id) from $msgRp where berpuserid =$userid and ispushed =false)  
            )
        as msgCount";
        return DB::select($sql);
        
    }
    
    
    /***
     * 获得 该用户的 所有 消息  并标识 其读写状态
     * @param int $userid
     */
    static public  function userMsg($userid,$lmt,$offset){
        
        $msgFollowT=msgAddFollow::$STable;
        $msgIcnBelikedT=msgIcnBelikedM::$STable;
        
        $msgCmtT=msgCommentM::$STable;
        $msgBeStoredT=msgBestoredM::$STable;
        
        $msgCmtBelikedT=msgCmtBelikedM::$STable;
        $msgRp=msgRpM::$STable;
  
        $sql=" select 
        
        foll.userid as fuserid,foll.followedid as followeduserid,
        icnlike.userid as icnlkuserid,icnlike.belikeduserid as icnlkeduserid,icnlike.iconid as icnid,
        
        cmt.becmtuserid as cmtbecmtuserid ,cmt.userid as cmtuserid,cmt.commentid as cmtid,cmt.iconid as cmticnid,
        st.userid as stuserid,st.bestoreduserid as stbestoreduserid,st.iconid as sticnid,
        
        cmtlk.userid as cmtlkuserid,cmtlk.belikeduserid as cmlkbelkuserid,cmtlk.cmtid as cmtlkid,
        rp.userid as rpuserid,rp.berpuserid as rpbeuserid,rp.cmtid as rpcmtid,rp.rpid as rpid
        
        from 
        $msgFollowT as foll,
        $msgIcnBelikedT as icnlike,
        
        $msgCmtT as cmt,
        $msgBeStoredT as st,
        
        $msgCmtBelikedT as cmtlk,
        $msgRp as rp
        
        where 
        
        (foll.userid=$userid ) and
        (icnlike.belikeduserid=$userid ) and
        
        (cmt.becmtuserid=$userid ) and
        (st.bestoreduserid=$userid) and
        
        (cmtlk.belikeduserid=$userid) and
        (rp.berpuserid=$userid)
        
         order by 
         
         foll.ispushed,
         icnlike.ispushed, 
         
         cmt.ispushed,
         st.ispushed,
         
         cmtlk.ispushed,
         rp.ispushed
        
        
        limit $lmt offset $offset   ";
        
        return DB::select($sql);
 
    }
    
    
    
    
    static public  function userMsg1($userid,$lmt,$offset){
    
        $msgFollowT=msgAddFollow::$STable;
        $msgIcnBelikedT=msgIcnBelikedM::$STable;
    
        $msgCmtT=msgCommentM::$STable;
        $msgBeStoredT=msgBestoredM::$STable;
    
        $msgCmtBelikedT=msgCmtBelikedM::$STable;
        $msgRp=msgRpM::$STable;
    
        $sql=" select
        
        foll.userid as fuserid,foll.followedid as followeduserid,
        icnlk.userid as icnlkuserid,icnlk.belikeduserid as icnlkeduserid,icnlk.iconid as icnlkid,
        
        cmt.becmtuserid as cmtbecmtuserid ,cmt.userid as cmtuserid,cmt.commentid as cmtid,cmt.iconid as cmticnid,
        st.userid as stuserid,st.bestoreduserid as stbestoreduserid,st.iconid as sticnid,
        
        cmtlk.userid as cmtlkuserid,cmtlk.belikeduserid as cmlkbelkuserid,cmtlk.cmtid as cmtlkid,
        rp.userid as rpuserid,rp.berpuserid as rpbeuserid,rp.cmtid as rpcmtid,rp.rpid as rpid
         
        from 
        (select userid ,followedid from $msgFollowT where userid = $userid 
            order by ispushed limit $lmt offset $offset) as foll,
        (select userid,belikeduserid,iconid from $msgIcnBelikedT where belikeduserid=$userid
            order by ispushed limit $lmt offset $offset ) as icnlk,
            
        (select becmtuserid,userid,commentid,iconid from $msgCmtT where becmtuserid=$userid
            order by ispushed limit $lmt offset $offset ) as cmt,
        (select userid,bestoreduserid,iconid from $msgBeStoredT where belikeduserid=$userid
            order by ispushed limit $lmt offset $offset ) as st,
            
        (select userid,belikeduserid,cmtid from $msgCmtBelikedT where cmtlk.belikeduserid=$userid
             order by ispushed limit $lmt offset $offset ) as cmtlk,
        (select userid ,berpuserid,cmtid,rpid from $msgRp where berpuserid=$userid
             order by ispushed limit $lmt offset $offset ) as rp ";
    
        echo $sql;
        return DB::select($sql);
    
    }
    
    
    
}

?>